/**
 * 在线抢答系统后台
 * 
 */

const xlsx = require('xlsx')


const url = require("url");
const XLSXWriter = require('xlsx-writestream');
const oxlsx = require('node-xlsx');
const path = require('path');
const Sequelize = require('sequelize');
const nodeExcel = require('excel-export');

// 引入express模块
const express = require("express");
var app = express();
var moment = require('moment');
var qs = require('qs');

// console.log(moment(new Date('2020-06-30T10:17:14.000Z')).format('YYYY-MM-DD HH:mm:ss'))

// 引入session支持
const session = require("express-session");
const cookieParser = require("cookie-parser");
var sessionOpts = {
	secret: 'a cool secret', // 设置密钥	
	resave: true,
	saveUninitialized: true,
	key: 'myapp_sid',
	cookie: {
		maxAge: 1000 * 60 * 50, // session保持的时间，单位是毫秒
		secure: false //加密
	}
}
app.use(session(sessionOpts));


// 引入数据库模块
const mysql = require("mysql");
const connection = mysql.createConnection({
	"host": "localhost",
	"port": 3309,
	"user": "root",
	"password": "",
	"database": "thetest",
	"multipleStatements": true //支持多语句查询

})
connection.connect();

// 引入body支持
const bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({
	extended: false
}));

// 引入文件上传
const multer = require("multer");
app.use(multer({
	dist: "/temp/"
}).any());

// 引入文件系统模块
const fs = require("fs");

// 声明静态资源文件夹
app.use("/static", express.static("static"));
app.use("/dist", express.static("dist"));

/*************express允许跨域****************/
app.all('*', function(req, res, next) {
	res.header('Access-Control-Allow-Origin', 'http://localhost:8080');
	// res.header('Access-Control-Allow-Origin', '*');

	// res.header('Access-Control-Allow-Headers', 'Content-Type');
	res.header('Access-Control-Allow-Headers',
		'Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild');
	res.header('Access-Control-Allow-Methods', '*');
	//将Access-Control-Allow-Credentials设为true
	res.header('Access-Control-Allow-Credentials', true);
	next();
});


// 上传/添加试题
app.post("/uploadTest", (req, resp) => {
	var body = req.body;
	var files = req.files;
	console.log(body);
	console.log(files);
	// console.log(files[0].buffer.toString())
	// console.log(typeof body.testTime);

	// 记录时间戳
	var code = new Date(body.testTime).getTime();
	console.log(code);
	// 日期格式的转换
	let tim = moment(new Date(body.testTime)).format('YYYY-MM-DD HH:mm:ss');
	console.log(tim)
	var sess = req.session;
	var user = sess.users;
	if (!user || user.role != 2) {
		console.log("您已被拦截");
		return;
	}
	var data = files[0].buffer.toString();
	var dataJson = JSON.parse(data);

	var respJson = {
		status: false,
		info: "",
		data: {}
	}

	// 处理时间格式
	// var d = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")format(body.testTime);
	// var host = "http://localhost:8083/static/" + files[0].originalname;
	var host = files[0].originalname;
	// teacher_id 在session中获取，，判断角色是否是老师，测试阶段使用模拟数据	
	var dir = __dirname + "/static/" + files[0].originalname;
	var sql = "insert into test_manage(teacher_id,grade,test_address,twice_time,test_time,course,test_code) values('" +
		user.username + "'," + body.grade + ",'" + host + "'," + body.time + ",'" + tim + "','" + body.course + "','" +
		code + "')";
	fs.writeFile(dir, data, (err) => {
		if (!err) {
			// 信息写入数据库
			connection.query(sql, (err, result, filed) => {
				if (!err) {
					respJson.status = true;
					respJson.info = "数据写入成功";
					resp.json(respJson);
				} else {
					console.log(err);
					respJson.info = "数据添加错误，请重新提交";
					resp.json(respJson);
				}
			})

		} else {
			console.log(err);
			respJson.info = "服务器出错啦";
			resp.json(respJson);
		}
	});
})



app.get("/login", function(request, response) {
	// 登录成功后把用户信息存到session中

	var query = url.parse(request.url, true).query; //query参数
	var resJson = {
		ok: false,
		info: "",
		data: {}
	}
	var users = {};
	var sess = null;
	// console.info(query);
	var sql = "select * from user where  username='" + query.username + "'and password='" + query.password + "' limit 1";

	connection.query(sql, function(err, result, filed) {
		
		// console.info(result[0].role)
		if (!err) {
			if (result.length > 0) {
				resJson.ok = true;
				users.username = result[0].username;
				var role = result[0].role;
				users.role = role;
				var tableName = null;
				var field = null;
				if (role == 2) {
					tableName = "teacher";
					field = "teacher_id";
				} else {
					tableName = "student";
					field = "student_code";
				}
				
				var sql2 = "select fullname from " + tableName + " where " + field + "='" + query.username + "' limit 1";
				connection.query(sql2, (err1, result1, fileds) => {
					if (!err) {
						if (result1.length > 0) {
							users.fullname = result1[0].fullname;
							sess = request.session;
							sess.users = users;
							resJson.user = sess.users;
							response.json(resJson);
						}
					}
				})
				// users.fullname = result[0].fullname;
				// console.log("真实姓名=  " + users.fullname)

			} else {
				console.log("您输入的用户名或密码不正确");
				resJson.info = "您输入的用户名或密码不正确";
				response.json(resJson);
			}
			console.log(sess);
		} else {
			resJson.info = "后台报错啦";
			response.json(resJson);
		}

	});
});

// 查看考试列表
app.get("/examLists", (req, resp) => {
	var sess = req.session;
	var user = sess.users;
	console.log(sess);
	if (!user || user.role != 3) {
		console.log("您的信息已被拦截");
		return;
	}
	var resJson = {
		ok: false,
		info: "",
		data: {}
	}
	// 查询学生的年级，用于判断有无该生的考试信息
	var sql =
		"SELECT * FROM student,test_manage WHERE SYSDATE() BETWEEN test_time  - INTERVAL 1 HOUR AND test_time +  INTERVAL (twice_time * 60) MINUTE AND student_code = '" +
		user.username + "' AND test_manage.grade = student.grade;";
	connection.query(sql, (err, result, filed) => {
		if (!err) {
			console.log(result);
			if (result.length > 0) {
				sess.users.fullname = result[0].fullname;
				resJson.data = result;
				console.log("考试时间：" + result[0].test_time);
				resp.json(resJson);
			} else {
				resJson.info = "不存在考试信息";
			}

		} else {
			console.log(err);
			resJson.info = "服务器出错啦";
			resp.json(resJson);
		}
	})

})



// 开始考试读取考试内容
app.get("/getContent", (req, resp) => {
	var query = url.parse(req.url, true).query;
	var respData = {
		status: false,
		info: "",
		data: {}
	}
	// 获取请求的url
	fs.readFile(__dirname + "/static/" + query.filename, (err, data) => {
		if (!err) {
			respData.status = true;
			respData.info = "请求成功";
			var content = JSON.parse(data.toString());
			// 删除正确答案
			for (let i = 0; i < content.exam_course.length; i++) {
				delete content.exam_course[i].key;
			}
			// console.log(content);
			respData.data = content;
			resp.json(respData);
		} else {
			console.log(err);
			resp.info = "请求出错啦";
			resp.json(respData);
		}
	})
})


// 学生考试时答案信息的保存
app.post("/writeFile", (req, resp) => {
	// console.log(req.body);
	// console.log(qs.parse(req.body));
	var sess = req.session;
	var user = sess.users;
	if (!user || !user.fullname) {
		return;
	}
	var data = qs.parse(req.body);
	var respData = {
		status: false,
		info: "",
		data: {}
	}
	// 把数据写入文件
	// 文件名：用户名+时间+姓名 + '.json';
	var dir = __dirname + "/static/" + user.username + "_" + user.fullname + "_" + data.exam_title + ".json";
	fs.writeFile(dir, JSON.stringify(data), (err) => {
		if (!err) {
			respData.info = "数据写入成功";
			respData.status = true;
			resp.json(respData);
		} else {
			console.log(err);
			respData.info = "服务器出错啦";
			resp.json(respData);
		}
	})
})

// 用户点击提交按钮/时间到了自动提交
app.post("/marking", (req, resp) => {
	var sess = req.session;
	var user = sess.users;
	if (!user || !user.fullname) {
		return;
	}

	var data = qs.parse(req.body);
	// 接收原始试卷名
	var filename = data.testname;
	var answer = JSON.stringify(data.datas.exam_course);
	var answers = JSON.parse(answer);
	var test_id = data.id;
	var course = data.course;
	var totalScore = 0; //存储总分
	// 计算每一题的分值
	var everyCount = 100 / answers.length;
	// 阅卷,
	var dir = __dirname + "/static/" + filename;

	var respData = {
		status: false,
		info: ""
	}

	fs.readFile(dir, (err, data) => {
		if (!err) {
			var keys = JSON.parse(data.toString());
			for (let i = 0; i < keys.exam_course.length; i++) {
				if (keys.exam_course[i].unit == answers[i].unit && keys.exam_course[i].key == answers[i].answer) {
					totalScore += everyCount;
				}
			}
			var sql = "insert into mark(username,course,stu_score,test_id) values('" + user.username + "','" + course + "'," +
				totalScore + "," + test_id + ")";
			connection.query(sql, (err, result, filed) => {
				if (!err) {
					console.log("数据插入成功");
					respData.info = "数据插入成功";
					respData.status = true;
					resp.json(respData);
				} else {
					console.log(err);
					respData.info = "数据库出错啦";
					resp.json(respData);
				}
			})
			console.log("总得分= " + totalScore)
		} else {
			console.log(err);
			respData.info = "服务器出错啦";
			resp.json(respData);
		}
	})

})



// 校验考生是否已经进行过考试任务,需要接收的参数：用户名，试卷编号
app.get("/getScore", (req, resp) => {
	var sess = req.session;
	var user = sess.users;
	if (!user) {
		console.log("该生没有登录");
		return;
	}
	var query = url.parse(req.url, true).query;
	// console.log("试题编号=  " + query.id);	
	var status = {
		isTest: true
	}
	var sql = "SELECT * FROM mark WHERE username='" + user.username + "' AND test_id = " + query.id + " LIMIT 1;";
	connection.query(sql, (err, result, filed) => {
		if (!err) {
			if (result.length > 0) {
				console.log("您已参加过该考试任务，不允许再次考试");
				status.isTest = false;
				resp.json(status);
			} else {
				console.log("可以参加考试");
				resp.json(status);
			}
		} else {
			console.log(err);
		}
	})
})

// 老师查询试卷列表，即考生试题
app.get("/testList", (req, resp) => {
	var sess = req.session;
	var user = sess.users;
	console.log(user);
	if (!user || user.role != 2) {
		console.log("您已被拦截")
		return;
	}
	var respData = {
		status: false,
		info: "",
		data: {},
		total: 1
	}
	// 计算好总记录数，总页数，以及每页显示的记录数
	// 从第几条记录开始查
	var query = url.parse(req.url, true).query;
	var currentPage = query.currentPage;
	var showRecord = 10;
	var record = (parseInt(currentPage) - 1) * showRecord;
	var sql1 = "select * from test_manage where teacher_id = '" + user.username + "'"
	var sql = sql1 + ";" + sql1 + " limit " + record + "," + showRecord + ";";
	connection.query(sql, (err, result, filed) => {
		if (!err) {
			respData.status = true;
			respData.info = "查询成功";
			console.log(result);
			var size = result[0].length; //数据库中的总记录数
			var total = (size + showRecord - 1) / showRecord;
			respData.total = total;
			respData.data = result[1];
			resp.json(respData);
		} else {
			respData.info = "数据库出错啦";
			console.log(err);
			resp.json(respData);
		}
	})
})

// 删除试卷,接收参数id
app.post("/delTest", (req, resp) => {
	var body = req.body;
	var sess = req.session;
	var user = sess.users;
	console.log("id=" + body.id);
	if (!body.id || !user) {
		return;
	}
	var sql = "delete from test_manage where id=" + body.id;
	connection.query(sql, (err, result, filed) => {
		if (!err) {
			resp.send("success");
		} else {
			console.log(err);
		}
	})

})


app.post("/LoginOut", (req, resp) => {
	// 销毁session
	var sess = req.session.destroy();
	resp.send("ok");
})

// connection.query({
// 	sql:`select * from student;
// 	select * from student limit 2,2;`
// },(err,result,filed)=>{
// 	if(!err){
// 		console.log(result);
// 	}else{
// 		console.log(err);
// 	}
// })

// 主页
app.get('/', function(request, response) {
	var query = url.parse(request.url, true).query;
	response.sendFile(__dirname + "/index.html");
});















/*
 *
 *林鹏飞写的 
 * */

var filespath = null;
app.post('/user', function(req, res) {

	var allFiles = req.files;
	console.log(allFiles);
	filespath = Math.floor(Math.random() * 10000);
	if (allFiles && allFiles.length > 0) {
		fs.readFile(allFiles[0].path, function(err, data) {
			if (!err) {
				var dir = __dirname + "/files/" + filespath + allFiles[0].originalname;
				// fs.writeFileSync(dir, data);

				new Promise(function(resolve, reject) {
					fs.writeFile(dir, data, function(err) {
						if (err) {
							console.info(err);
						} else {
							console.info(data)
							resolve("成功");
						}
					})

				}).then(function(res) {
					console.log("25352323");
					//workbook就是xls文档对象
					console.info(dir);
					let workbook = xlsx.readFile(__dirname + "/files/" + filespath + "lpf.xlsx"); //workbook就是xls文档对象
					let sheetNames = workbook.SheetNames; //获取表明
					let sheet = workbook.Sheets[sheetNames[0]]; //通过表明得到表对象
					var data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
					console.info(data);
					data.forEach(function(item, index) {

						var fullname = item['姓名'];
						var classname = item['班级名'];
						var nation = item['名族'];
						var phone = item['手机'];
						var sex = item['性别'];
						var student_code = item['学生编号'];
						var grade = item['年级'];

						connection.query(
							`insert into student (fullname, classname, nation,phone,sex,student_code,grade) values('${fullname}' ,'${classname}' ,'${nation}',${phone},'${sex}', ${student_code},'${grade}' )`,
							function(err, result, filed) {
								if (err) {
									console.info(err);
								} else {
									console.info(result);
								}
							});
						connection.query(
							`insert into user (username, password, role) values('${student_code}' ,'${student_code}' ,'3' )`,
							function(err, result, filed) {
								if (err) {
									console.info(err);
								} else {
									console.info(result);
								}
							});




					})
					// alter table `tablename` auto_increment=1; 重新自增

				}).catch(function(err) {
					console.log(err);
				})
				// insert into 表名 (字段1, 字段2, 字段3) values
				// 　　　　　　(字段1值, 字段2值, 字段3值),

				// 　　　　　　(字段1值, 字段2值, 字段3值)


			}
			// res.json({
			// 	ok: true,
			// 	info: "测试",
			// 	data: {}
			// })

		})

	}


	res.send("ssddddddd");


});

app.get('/marksteacher', function(req, res) {
		connection.query('select * from mark as a,student as b where a.username = b.student_code ', function(err, result,
			filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send(result);
			}
		})

	}),

app.get('/marks', function(req, res) {
		var query = url.parse(req.url, true).query;
		connection.query(
			`select * from mark as a,student as b where a.username = b.student_code and a.username=${query.lingetsessionuser}`,
			function(err, result, filed) {
				if (err) {
					console.info(err);
				} else {
					console.info(result);
					res.send(result);
				}
			})

	}),
	
	// 
app.get('/outexcelscore', function(req, res) {
		var query = url.parse(req.url, true).query;
		connection.query(
			`select * from mark as a,student as b where a.username = b.student_code and a.username=${query.lingetsessionuser}`,
			function(err, result, filed) {
				if (err) {
					console.info(err)
				} else {
					var scorle_excell = Math.floor(Math.random() * 10000);
					const conf = {};
					// 定义sheet名称
					conf.name = "DBData";
					// 定义列的名称以及数据类型
					conf.cols = [{
						caption: '姓名',
						type: 'string'
					}, {
						caption: '课程',
						type: 'string'
					}, {
						caption: '分数',
						type: 'number'
					}];
					conf.rows = [];

					// 定义row的数据

					result.forEach(function(item) {
						var rowslin = [item.fullname, item.course, item.stu_score]
						conf.rows.push(rowslin);
					})
					console.info(conf.rows)
					const results = nodeExcel.execute(conf);
					console.log(typeof results);
					// fs将文件写到内存
					fs.writeFile(__dirname + "/scorle_excel/" + scorle_excell + '.xlsx', results, 'binary', (err) => {
						err ? console.log(err) : null;
					});
					res.send("导出成功")
				}
			})

	}),
	
	
	// 导出学生信息表
app.get('/outexcelstudent', function(req, res) {
		connection.query("select *from student", function(err, result, filed) {
			if (err) {
				console.info(err)
			} else {
				console.info(result)
				var student_excell = Math.floor(Math.random() * 100000);
				const conf = {};
				// 定义sheet名称
				conf.name = "DBData";
				// 定义列的名称以及数据类型
				conf.cols = [{
						caption: '姓名',
						type: 'string'
					}, {
						caption: '班级名',
						type: 'string'
					}, {
						caption: '名族',
						type: 'string'
					},
					{
						caption: '手机',
						type: 'number'

					},
					{
						caption: '性别',
						type: 'string'

					},
					{
						caption: '学生编号',
						type: 'string'

					},
					{
						caption: '年级',
						type: 'string'

					}
				];
				conf.rows = [];

				// 定义row的数据

				result.forEach(function(item) {
					var rowslin = [item.fullname, item.classname, item.nation, item.phone, item.sex, item.student_code, item.grade]
					conf.rows.push(rowslin);
				})
				console.info(conf.rows)
				const results = nodeExcel.execute(conf);
				console.log(typeof results);
				// fs将文件写到内存
				fs.writeFile(__dirname + "/student_excel/" + student_excell + '.xlsx', results, 'binary', (err) => {
					err ? console.log(err) : null;
				});
				res.send('成功上传')
			}
		})
	}),


app.get('/userpolin', function(req, res) {
		var query = url.parse(req.url, true).query;
		console.info(query.index)
		var indexx = query.index;
		var limitlin = 20;
		var offertlin = (indexx - 1) * 20;
		connection.query(`select *from student limit ${limitlin} offset ${offertlin} `, function(err, result, filed) {
			if (err) {
				console.info(err);
			} else {
				res.send(result);

			}
		})
	})


app.get('/userpo', function(req, res) {
	var query = url.parse(req.url, true).query;
	connection.query(`select *from student `, function(err, result, filed) {
		if (err) {
			console.info(err);
		} else {
			res.send(result);

		}
	})
})
// 主页
app.get('/userde', function(req, res) {
	var query = url.parse(req.url, true).query;
	connection.query(`delete from student where id=${query.id}`, function(err, result, filed) {
		if (err) {
			console.info(err);
		} else {
			console.info(result);

			res.send("成功删除");
		}
	})

})

app.get('/update', function(req, res) {
	var query = url.parse(req.url, true).query;
	console.info(query.id);
	console.info(query.checklin)
	if (query.checklin == 'okfull') {
		connection.query(`update student  set  fullname = '${query.fullname}'   where id=${query.id}`, function(err, result,
			filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改名字成功");
			}
		});
	};
	if (query.checklin == 'oksex') {
		connection.query(`update student  set  sex = '${query.sex}' where  id=${query.id}`, function(err, result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改性别成功");
			}
		});
	};
	if (query.checklin == 'okclass') {
		connection.query(`update student  set  classname = '${query.classname}' where id=${query.id}`, function(err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改 成功");
			}
		})
	};
	if (query.checklin == 'oknation') {
		connection.query(`update student  set  nation = '${query.nation}'   where id=${query.id}`, function(err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改 noation成功");
			}
		})
	};
	if (query.checklin == 'okphone') {
		connection.query(`update student  set  phone = '${query.phone}'   where id=${query.id}`, function(err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改 phone成功");
			}
		})
	};
	if (query.checklin == 'okstudent_code') {
		connection.query(`update student  set  student_code = '${query.student_code}'   where id=${query.id}`, function(
			err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改 student_code成功");
			}
		})
	};
	if (query.checklin == 'okgrade') {
		connection.query(`update student  set  grade = '${query.grade}'   where id=${query.id}`, function(err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("修改 grade成功");
			}
		})
	}

})





app.post('/useradd', function(req, res) {
	console.info(req.body);
	var body = req.body;
	connection.query(
		`insert into student (fullname,classname,nation,phone,sex,student_code,grade) values ('${body.fullname}','${body.classname}','${body.nation}',${body.phone},'${body.sex}',${body.student_code},'${body.grade}')`,
		function(err,
			result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
				res.send("增加成功")
			}
		});
	connection.query(
		`insert into user (username, password, role) values(${body.student_code} ,${body.student_code} ,'3' )`,
		function(err, result, filed) {
			if (err) {
				console.info(err);
			} else {
				console.info(result);
			}
		});


})
// 屏蔽 favico.ico错误
app.get('/favico.ico', (req, res) => {
	res.sendStatus(404);
});


// 蓝广富写



// 修改信息
app.post("/changefor",function(request,response){
	var qiufan = request.body;
	var sess = request.session;
	var user = sess.users;
	var data = {
		ok:false,
		info:""
	}
	// console.log(user);
	if(!user){
		console.log("您已被拦截")
		return;
	}
	var sql3 = "select * from user where username = '"+user.username+"' and password = '"+ qiufan.used +"' limit 1";
	connection.query(sql3,function(err,result,filed){
		if(!err){
			console.info(result);
			if(result.length>0){
				data.ok = true;
			}
			response.json(data);
		}else{
			console.info(err)
		}
	});
})

// 原密码正确时向后端发起修改密码的请求
app.post("/modify",function(request,response){
	var aa =request.body;
	// console.info(aa.abc);
	var sess = request.session;
	var user = sess.users;
	var data2 ={
		ok:false,
		info:""
	}
	var sql4 = "update user set password = '"+aa.abc+"' where username = '"+user.username+"'"
	connection.query(sql4,function(err,result,filed){
		if(!err){
			data2.ok = true;
			response.json(data2);
		}else{
			console.info(err);
		}
	})
})





//结束 蓝
// 退出登录



// 启动服务接口
var server = app.listen(8083, function() {
	console.log("考试系统后台服务成功启动，监听端口：" + server.address().port)
})
